using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;
namespace DAO
{
   public static class hocsinhDAO
   {
       #region le quan dai
       //danh sach ket qua
       public static DataTable danhsachketqua(ketquaDTO ketquadto)
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select hs.mahs as [Mã HS],hoten as [Tên HS],diemmieng as [Điểm miệng],diem15 as [Điểm 15'],diem1tiet1 as [Điểm 1tiết 1],diem1tiet2 as [Điểm 1tiết 2],diemthi as [Điểm thi] from hocsinh hs,ketqua k,lop l where tinhtrang='Còn học'and hs.mahs=k.mahs and k.malop=l.malop and l.malop='" + ketquadto.Malop + "' and mahk='" + ketquadto.Mahk + "' and mamh='" + ketquadto.Mamh + "'";
           return Dataprovider.laythongtinbang(sql, con);
       }
       
       //danh sach nhap diem
       public static DataTable danhsachnhapdiem(ketquaDTO ketquadto)
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select mahs as [Mã HS],hoten as [Tên HS] from hocsinh hs where tinhtrang='Còn học' and malop='" + ketquadto.Malop + "'";
           return Dataprovider.laythongtinbang(sql, con);
       }
      
       //them hoc sinh
       public static bool themhocsinh(hocsinhDTO hocsinhDTO)
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "insert into hocsinh(mahs,hoten,gioitinh,ngaysinh,diachi,noisinh,malop,mahl,mahanhkiem,tinhtrang) values('" + hocsinhDTO.Mahs + "','" + hocsinhDTO.Hoten + "','" + hocsinhDTO.Gioitinh + "','" + hocsinhDTO.Ngaysinh + "','" + hocsinhDTO.Diachi + "','" + hocsinhDTO.Noisinh + "','" + hocsinhDTO.Malop + "','" + hocsinhDTO.Mahl + "','" + hocsinhDTO.Mahanhkiem + "','" + hocsinhDTO.Tinhtrang + "')";
           
           return Dataprovider.chaycaulenh(sql, con);
          
           
       }
       //xoa hoc sinh
       public static bool xoahocsinh(hocsinhDTO hocsinhDTO)
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "update hocsinh set tinhtrang='Xóa tên' where mahs='" + hocsinhDTO.Mahs + "'";
           return Dataprovider.chaycaulenh(sql, con);


       }
       //sua hoc sinh
       public static bool capnhathocsinh(hocsinhDTO hocsinhDTO)
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "update hocsinh set hoten='"+hocsinhDTO.Hoten+"',gioitinh='"+hocsinhDTO.Gioitinh+"',ngaysinh='"+hocsinhDTO.Ngaysinh+"',diachi='"+hocsinhDTO.Diachi+"',noisinh='"+hocsinhDTO.Noisinh+"',malop='"+hocsinhDTO.Malop+"',mahanhkiem='"+hocsinhDTO.Mahanhkiem+"',tinhtrang='"+hocsinhDTO.Tinhtrang+"' where mahs='"+hocsinhDTO.Mahs+"'";
           return Dataprovider.chaycaulenh(sql, con);
               


       }
       //danh sach lop
       public static DataTable danhsachlop()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from lop";
           return Dataprovider.laythongtinbang(sql, con);
       }
       //danh sach mon hoc
       public static DataTable danhsachmonhoc()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from monhoc";
           return Dataprovider.laythongtinbang(sql, con);
       }
       
       //danh sach hoc ki
       public static DataTable danhsachhocki()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from hocki";
           return Dataprovider.laythongtinbang(sql, con);
       }
       //danh sach hanh kiem
       public static DataTable danhsachhanhkiem()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from hanhkiem";
           return Dataprovider.laythongtinbang(sql, con);
       }
       //danh sach hoc sinh
       public static DataTable danhsachhocsinh()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from hocsinh  ";
           return Dataprovider.laythongtinbang(sql,con);
       }
       //danh sach hoc sinh con hoc
       public static DataTable danhsachhocsinhcon()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from hocsinh where tinhtrang='Còn học' ";
           return Dataprovider.laythongtinbang(sql, con);
       }
       
       //them diem
       public static bool themdiem(ketquaDTO ketquadto)
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "insert into ketqua(mahs,mamh,mahk,malop,diemmieng,diem15,diem1tiet1,diem1tiet2,diemthi) values('" + ketquadto.Mahs + "','" + ketquadto.Mamh + "','" + ketquadto.Mahk + "','" + ketquadto.Malop + "','" + ketquadto.Diemmieng + "','" + ketquadto.Diem15 + "','" + ketquadto.Diem1tiet1 + "','" + ketquadto.Diem1tiet2 + "','" + ketquadto.Diemthi + "')";

           return Dataprovider.chaycaulenh(sql, con);


       }
       //sua diem
       public static bool suadiem(ketquaDTO ketquadto)
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "update ketqua set diemmieng='" + ketquadto.Diemmieng + "',diem15='" + ketquadto.Diem15 + "',diem1tiet1='" + ketquadto.Diem1tiet1 + "',diem1tiet2='" + ketquadto.Diem1tiet2 + "',diemthi='" + ketquadto.Diemthi + "' where mahs='"+ketquadto.Mahs+"' and mamh='" + ketquadto.Mamh + "'and mahk='" + ketquadto.Mahk + "'and malop='" + ketquadto.Malop + "'";
           return Dataprovider.chaycaulenh(sql, con);
       }
       //danh sach khoi
       public static DataTable danhsachkhoi()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from khoi";
           return Dataprovider.laythongtinbang(sql, con);
       }
       //danh sach lop theo khoi
       public static DataTable danhsachloptheokhoi(KhoiDTO khoidto)
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select k.makhoi,tenkhoi,malop,tenlop from lop l,khoi k where k.makhoi=l.makhoi and k.makhoi='"+khoidto.Makhoi+"'";
           return Dataprovider.laythongtinbang(sql, con);
       }
       //danh sach giao vien
       public static DataTable danhsachgiaovien()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from giaovien where trangthai='Còn Dạy'";
           return Dataprovider.laythongtinbang(sql, con);
       }
       //danh sach admin
       public static DataTable danhsachadmin()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from admin where trangthai='Hoạt động'";
           return Dataprovider.laythongtinbang(sql, con);
       }
       #endregion

       #region Nguyen Thanh Duy

       public static DataTable danhSachHocLuc()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from hocluc";
           return Dataprovider.laythongtinbang(sql, con);
       }
       public static DataTable danhSachKetQua()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from ketqua";
           return Dataprovider.laythongtinbang(sql, con);
       }
       public static DataTable danhSachKhoi()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from khoi";
           return Dataprovider.laythongtinbang(sql, con);
       }
       public static DataTable danhSachNamHoc()
       {
           OleDbConnection con = Dataprovider.ketnoicsdl();
           string sql = "select * from NamHoc";
           return Dataprovider.laythongtinbang(sql, con);
       }
       #endregion
   }
}
